# -*- coding: utf-8 -*-
import os
import sqlite3
from datetime import datetime

def judge_sql(sql):
    '''判断sql类型'''
    sql = sql.strip().lower()
    ret = 'fetch'
    if sql.startswith('select'):
        # select
        pass
    elif sql.startswith('update'):
        # update
        ret = 'rowcount'
    elif sql.startswith('delete'):
        # delete
        ret = 'rowcount'
    elif sql.startswith('insert'):
        # insert
        ret = 'lastrowid'
    else:
        # create-table, drop-table
        ret = 'table'
    return ret

def db_execute(sql, params=None, fetch_type='one', many=False, db_file=None):
    '''
    执行数据库操作
        sql: SQL语句
        params: SQL语句需要的参数，默认为空（None）
        fetch_type: 当执行查询语句（select）时，获取数据的方法，有以下几种取值情况
            'one': 调用 fetchone()，默认
            'all': 调用 fetchall()
            正整数N: 表示要获取的数据行数，调用 fetchmany(N)
        many: 是否执行 executemany()，默认否，执行 execute()
        db_file: 数据库文件，默认为启动目录下的 'notebook.db'
    '''
    if db_file is None:
        db_file = os.path.join(os.getcwd(), 'notebook.db')
    # 连接数据库
    conn = sqlite3.connect(db_file)
    # 修改查询数据返回格式
    def dict_factory(cursor, row):
        '''修改查询格式'''
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d
    conn.row_factory = dict_factory
    # 创建游标
    cur = conn.cursor()
    # 执行操作
    if many:
        if params is None:
            params = []
        cur.executemany(sql, params)
    else:
        if params is None:
            params = ()
        cur.execute(sql, params)
    # 判断SQL类型
    sql_type = judge_sql(sql)
    ret = None # 返回值
    if sql_type in ('rowcount', 'lastrowid'):
        # 修改表的语句
        # 获取受影响的行数
        ret = cur.rowcount
        if ret > 0 and sql_type == 'lastrowid':
            # 如果insert成功，返回insert-id
            ret = cur.lastrowid
        # 提交修改
        conn.commit()
    elif sql_type == 'fetch':
        # 查询数据的语句，需要调用fetch开头的方法，获取数据
        if fetch_type == 'one':
            # 查询一条数据
            ret = cur.fetchone()
        elif fetch_type == 'all':
            # 查询所有数据
            ret = cur.fetchall()
        else:
            # 查询N条数据
            ret = cur.fetchmany(fetch_type)
    else:
        # 创建表的语句，忽略返回值
        pass
    # 关闭游标
    cur.close()
    # 关闭连接
    conn.close()

    return ret

# ######################### 我是分割线 ################################

def get_note_list():
    '''查询所有笔记'''
    sql = '''
    SELECT *
    FROM tb_note
    '''
    data = db_execute(sql, fetch_type='all')
    return data

def get_tag_list():
    '''查询所有标签'''
    sql = '''
    SELECT *
    FROM tb_tag
    '''
    data = db_execute(sql, fetch_type='all')
    return data

def get_tag_by_name(name):
    '''根据id查询标签'''
    sql = '''
    SELECT *
    FROM tb_tag
    WHERE name = ?
    '''
    params = (name,)
    data = db_execute(sql, fetch_type='one', params=params)
    return data

def get_note_by_id(note_id):
    '''根据id查询笔记'''
    sql = '''
    SELECT *
    FROM tb_note
    WHERE id = ?
    '''
    params = (int(note_id),)
    data = db_execute(sql, fetch_type='one', params=params)
    # 查询笔记绑定的标签
    sql = '''
    SELECT tb_tag.*
    FROM tb_tag
    LEFT JOIN tb_tag_note_map
    ON tb_tag.id = tb_tag_note_map.tag_id
    WHERE tb_tag_note_map.note_id = ?
    '''
    params = (data['id'],)
    data_tags = db_execute(sql, params=params, fetch_type='all')
    # 只取标签名列表
    tags_name = [item['name'] for item in data_tags]
    data['tags'] = tags_name
    return data

def is_tag_exists(tag_name):
    '''根据标签名判断标签是否存在'''
    sql = '''
    SELECT COUNT(1) AS tag_count
    FROM tb_tag
    WHERE name = ?
    '''
    params = (tag_name,)
    data = db_execute(sql, params=params, fetch_type='one')
    tag_count = data['tag_count']
    return tag_count > 0

def is_note_exists_by_id(note_id):
    '''根据笔记ID判断笔记是否存在'''
    sql = '''
    SELECT COUNT(1) AS note_count
    FROM tb_note
    WHERE id = ?
    '''
    params = (note_id,)
    data = db_execute(sql, params=params, fetch_type='one')
    note_count = data['note_count']
    return note_count > 0

def get_note_exist_tag_ids(note_id):
    '''获取笔记已经存在了的ID'''
    sql = '''
    SELECT tag_id
    FROM tb_tag_note_map
    WHERE note_id = ?
    '''
    params = (note_id,)
    data = db_execute(sql, params=params, fetch_type='all')
    # 只取出标签id
    return [item['tag_id'] for item in data]

def note_add_tags(note_id, tag_list):
    '''笔记添加标签'''
    sql = '''
    INSERT INTO tb_tag_note_map(tag_id, note_id)
    VALUES(?, ?)
    '''
    params = [(tag_id, note_id) for tag_id in tag_list]
    affect = db_execute(sql, params=params, many=True)
    return affect

def note_remove_tags(note_id, tag_list):
    '''笔记移除标签'''
    sql = '''
    DELETE FROM tb_tag_note_map
    WHERE note_id = ?
        AND tag_id IN({0})
    '''.format(repr(tag_list)[1:-1])
    params = (note_id,)
    affect = db_execute(sql, params=params)
    return affect

def update_or_insert_note(title, content, tags, note_id=None):
    # 获取今天的日期（字符串，YYYY-mm-dd格式）
    day = datetime.now().strftime('%Y-%m-%d')
    # 如果提交的数据中有标签，则处理tags
    tag_ids = [] # 存放请求的标签的id
    if bool(tags):
        # 筛选函数
        func = lambda item: bool(item)
        # 将空格分隔的字符串标签转化为列表
        tags_arr = list(filter(func, tags.split(' ')))
        # 将所有标签转化为小写
        tags_arr = list(map(lambda item: item.lower(), tags_arr))
        # 确保所有tag已经存在于数据库中了
        for tag_name in tags_arr:
            if not is_tag_exists(tag_name):
                # 如果数据库中不存在这个tag，就insert
                sql_tag = '''
                INSERT INTO tb_tag(name)
                VALUES(?)
                '''
                params = (tag_name,)
                # 执行insert操作
                tag_id = db_execute(sql_tag, params)
                print(tag_id, type(tag_id))
                if tag_id > 0:
                    tag_ids.append(tag_id)
                    print('添加tag [{0}] 成功'.format(tag_name))
                else:
                    print('添加tag [{0}] 失败'.format(tag_name))
            else:
                exist_tag = get_tag_by_name(tag_name)
                tag_ids.append(exist_tag['id'])
    # 根据是否存在笔记id，判断是新建笔记，还是修改现有的笔记
    if bool(note_id):
        # 如果有笔记id，说明是修改
        sql = '''
        UPDATE tb_note
        SET title = ?,
            content = ?,
            day = ?
        WHERE id = ?
        '''
        params = (title, content, day, note_id)
        # 执行update操作
        affect = db_execute(sql, params)
        if affect < 1:
            return False
    else:
        # 如果无id，说明是新建
        sql = '''
        INSERT INTO tb_note(title, content, day)
        VALUES(?, ?, ?)
        '''
        params = (title, content, day)
        # 执行update操作
        note_id = db_execute(sql, params)
        if note_id < 1:
            return False
    # 同步笔记所绑定的标签
    # 新建笔记时，添加标签关联
    # 修改笔记时，如果修改了标签，删除不存在的关联，添加新的关联
    # 查询笔记绑定了的标签
    exist_tag_ids = get_note_exist_tag_ids(note_id)
    # 无需修改的标签
    origin_ids = list(set(exist_tag_ids) & set(tag_ids))
    # 新添加的标签
    new_ids = list(set(tag_ids) - set(exist_tag_ids))
    # 添加标签
    if bool(new_ids):
        note_add_tags(note_id, new_ids)
    # 需要移除的标签
    old_ids = list(set(exist_tag_ids) - set(tag_ids))
    # 移除标签
    if bool(old_ids):
        note_remove_tags(note_id, old_ids)
    return True

# ######################### 我是分割线 ################################

if __name__ == '__main__':
    # 创建tag表
    sql_crete_tag = '''
    CREATE TABLE IF NOT EXISTS `tb_tag` (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT,
        `name` TEXT NOT NULL UNIQUE
    )
    '''
    db_execute(sql_crete_tag)
    # 创建note表
    sql_create_note = '''
    CREATE TABLE IF NOT EXISTS `tb_note` (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT,
        `title` TEXT NOT NULL,
        `content` TEXT,
        `tags` TEXT,
        `day` TEXT NOT NULL,
        `pwd` TEXT
    )
    '''
    db_execute(sql_create_note)
    # tag与note关联表
    sql_crete_tag_note_map = '''
    CREATE TABLE IF NOT EXISTS `tb_tag_note_map` (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT,
        `note_id` INTEGER NOT NULL,
        `tag_id` INTEGER NOT NULL
    )
    '''
    db_execute(sql_crete_tag_note_map)
